var express = require('express')
var router = express.Router()
// 导入自己封装的数据库操作模块
const exec = require('../db')
// 导入调试的标记
const { debug } = require('../config')

router.get('/', async function (req, res, next) {
  // 获取请求参数
  let query = req.query
  // 获取请求数据
  let body = req.body
  // 创建sql语句
  let sql
  if (query.page && query.size) {
    /* 分页查询分类 */
    // GET /categorys/?page=1&size=5
    let sql = `select count(*) as total from category_tb`
    let { total } = (await exec(sql))[0] // { total: 9 }
    sql = `select * from category_tb ORDER BY id desc limit ${
      query.size * (query.page - 1)
    },${query.size}`

    // 数据响应
    try {
      res.send({
        code: 0,
        msg: '查询成功',
        result: { total, category: await exec(sql) },
      })
      return
    } catch (err) {
      res.send({
        code: 1,
        msg: '失败',
        result: debug ? err : '',
      })
      return
    }
  } else {
    /* 查询所有分类 */
    // GET /categorys/
    sql = `select a.*, ifnull(b.num,0) num from  category_tb a left join (select category_tb.*,COUNT(*) num  from category_tb, article_tb where category_tb.id=article_tb.category_id && article_tb.deleted_time is null GROUP BY id) b on a.id=b.id`
  }
  // 返回数据库操作结果
  try {
    res.send({
      code: 0,
      msg: '查询成功!',
      result: await exec(sql),
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '查询失败!',
      result: debug ? err : '',
    })
  }
})

router.post('/', async function (req, res, next) {
  /* 添加一个分类 */
  // POST /categorys/
  // 获取请求数据
  let body = req.body
  // 创建sql语句
  let sql = `insert into category_tb (category_name) values ("${body.category_name}")`
  // 返回数据库操作结果
  try {
    let data = await exec(sql)
    res.send({
      code: 0,
      msg: '添加成功!',
      result: data.insertId,
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '添加失败!',
      result: debug ? err : '',
    })
  }
})

router.put('/', async function (req, res, next) {
  /* 根据id修改分类 */
  // PUT /categorys/
  // 获取请求参数
  let query = req.query
  // 获取请求数据
  let body = req.body
  // 过滤需要修改的数据
  let bodyfilter = ''
  for (let key in body) {
    if (body[key] !== '') {
      bodyfilter += `${key}="${body[key]}",`
    }
  }
  bodyfilter = bodyfilter.slice(0, bodyfilter.length - 1)
  // 创建sql语句
  let sql = `update category_tb set ${bodyfilter} where id=${query.id}`
  // 返回数据库操作结果
  try {
    let data = await exec(sql)
    res.send({
      code: 0,
      msg: '修改成功!',
      result: '',
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '修改失败!',
      result: debug ? err : '',
    })
  }
})

router.delete('/', async function (req, res, next) {
  /* 根据id删除用户信息 */
  // DELETE /categorys/
  // 获取请求参数
  let query = req.query
  // 创建sql语句
  let sql = `delete from category_tb where id=${query.id}`
  // 返回数据库操作结果
  try {
    let data = await exec(sql)
    res.send({
      code: 0,
      msg: '删除成功!',
      result: '',
    })
  } catch (err) {
    res.send({
      code: 1,
      msg: '删除失败!',
      result: debug ? err : '',
    })
  }
})

// 导出路由对象
module.exports = router
